package com.oceanstar.mapper;

import com.oceanstar.Pojo.*;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface DiseaseStatsMapper {

    // 统计老人总数 - 带条件筛选
    @Select("<script>" +
            "SELECT COUNT(DISTINCT u.id) FROM userinfo_table u " +
            "<where>" +
            "  u.identity = 1 " +
            "  <if test='query.gender != null'> AND u.gender = #{query.gender} </if>" +
            "  <if test='query.minAge != null or query.maxAge != null'>" +
            "    AND (SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE(" +
            "      CASE " +
            "        WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2))" +
            "        WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2))" +
            "      END, '%Y-%m-%d'), CURDATE())) BETWEEN " +
            "      <choose>" +
            "        <when test='query.minAge != null and query.maxAge != null'>#{query.minAge} AND #{query.maxAge}</when>" +
            "        <when test='query.minAge != null'>#{query.minAge} AND 150</when>" +
            "        <when test='query.maxAge != null'>0 AND #{query.maxAge}</when>" +
            "      </choose>" +
            "  </if>" +
            "  <if test='query.gridId != null'>" +
            "    AND u.gridId = #{query.gridId}" +
            "  </if>" +
            "</where>" +
            "</script>")
    Integer countTotalElderly(@Param("query") DiseaseStatsQuery query);

    // 统计有重大疾病的老人数量 - 带条件筛选
    @Select("<script>" +
            "SELECT COUNT(DISTINCT h.id) FROM healthinfo h " +
            "INNER JOIN userinfo_table u ON h.id = u.id " +
            "<where>" +
            "  u.identity = 1 " +
            "  AND h.medical_history IS NOT NULL AND h.medical_history != '' " +
            "  <if test='query.gender != null'> AND u.gender = #{query.gender} </if>" +
            "  <if test='query.minAge != null or query.maxAge != null'>" +
            "    AND (SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE(" +
            "      CASE " +
            "        WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2))" +
            "        WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2))" +
            "      END, '%Y-%m-%d'), CURDATE())) BETWEEN " +
            "      <choose>" +
            "        <when test='query.minAge != null and query.maxAge != null'>#{query.minAge} AND #{query.maxAge}</when>" +
            "        <when test='query.minAge != null'>#{query.minAge} AND 150</when>" +
            "        <when test='query.maxAge != null'>0 AND #{query.maxAge}</when>" +
            "      </choose>" +
            "  </if>" +
            "  <if test='query.gridId != null'>" +
            "    AND u.gridId = #{query.gridId}" +
            "  </if>" +
            "  <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "    AND EXISTS (" +
            "      SELECT 1 FROM (" +
            "        SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(h.medical_history, ',', numbers.n), ',', -1)) as disease " +
            "        FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 " +
            "              UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) numbers " +
            "        WHERE CHAR_LENGTH(h.medical_history) - CHAR_LENGTH(REPLACE(h.medical_history, ',', '')) >= numbers.n - 1" +
            "      ) split_diseases " +
            "      WHERE split_diseases.disease IN " +
            "      <foreach collection='query.diseaseList' item='disease' open='(' separator=',' close=')'>" +
            "        #{disease}" +
            "      </foreach>" +
            "    )" +
            "  </if>" +
            "  <if test='query.begin != null'> AND h.update_time >= #{query.begin} </if>" +
            "  <if test='query.end != null'> AND h.update_time &lt;= DATE_ADD(#{query.end}, INTERVAL 1 DAY) </if>" +
            "</where>" +
            "</script>")
    Integer countMajorDiseaseElderly(@Param("query") DiseaseStatsQuery query);

    // 最近30天新增病例 - 带条件筛选
    @Select("<script>" +
            "SELECT COUNT(DISTINCT h.id) FROM healthinfo h " +
            "INNER JOIN userinfo_table u ON h.id = u.id " +
            "<where>" +
            "  u.identity = 1 " +
            "  AND h.medical_history IS NOT NULL " +
            "  AND h.update_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) " +
            "  <if test='query.gender != null'> AND u.gender = #{query.gender} </if>" +
            "  <if test='query.minAge != null or query.maxAge != null'>" +
            "    AND (SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE(" +
            "      CASE " +
            "        WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2))" +
            "        WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2))" +
            "      END, '%Y-%m-%d'), CURDATE())) BETWEEN " +
            "      <choose>" +
            "        <when test='query.minAge != null and query.maxAge != null'>#{query.minAge} AND #{query.maxAge}</when>" +
            "        <when test='query.minAge != null'>#{query.minAge} AND 150</when>" +
            "        <when test='query.maxAge != null'>0 AND #{query.maxAge}</when>" +
            "      </choose>" +
            "  </if>" +
            "  <if test='query.gridId != null'>" +
            "    AND u.gridId = #{query.gridId}" +
            "  </if>" +
            "  <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "    AND (" +
            "    <foreach collection='query.diseaseList' item='disease' separator=' OR '>" +
            "      h.medical_history LIKE CONCAT('%', #{disease}, '%')" +
            "    </foreach>" +
            "    )" +
            "  </if>" +
            "  <if test='query.begin != null'> AND h.update_time >= #{query.begin} </if>" +
            "  <if test='query.end != null'> AND h.update_time &lt;= DATE_ADD(#{query.end}, INTERVAL 1 DAY) </if>" +
            "</where>" +
            "</script>")
    Integer countNewCasesLast30Days(@Param("query") DiseaseStatsQuery query);

    // 获取预设的重大疾病列表
    @Select("SELECT disease_name FROM major_disease_config WHERE is_active = 1")
    List<String> selectMajorDiseaseList();

    // 查询疾病统计 - 带条件筛选
    @Select("<script>" +
            "SELECT " +
            "  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(h.medical_history, ',', numbers.n), ',', -1)) as diseaseName, " +
            "  COUNT(DISTINCT h.id) as count " +
            "FROM healthinfo h " +
            "INNER JOIN userinfo_table u ON h.id = u.id " +
            "JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 " +
            "      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) numbers " +
            "  ON CHAR_LENGTH(h.medical_history) - CHAR_LENGTH(REPLACE(h.medical_history, ',', '')) >= numbers.n - 1 " +
            "<where>" +
            "  u.identity = 1 " +
            "  AND h.medical_history IS NOT NULL AND h.medical_history != '' " +
            "  AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(h.medical_history, ',', numbers.n), ',', -1)) != '' " +
            "  <if test='query.gender != null'> AND u.gender = #{query.gender} </if>" +
            "  <if test='query.minAge != null or query.maxAge != null'>" +
            "    AND (SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE(" +
            "      CASE " +
            "        WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2))" +
            "        WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2))" +
            "      END, '%Y-%m-%d'), CURDATE())) BETWEEN " +
            "      <choose>" +
            "        <when test='query.minAge != null and query.maxAge != null'>#{query.minAge} AND #{query.maxAge}</when>" +
            "        <when test='query.minAge != null'>#{query.minAge} AND 150</when>" +
            "        <when test='query.maxAge != null'>0 AND #{query.maxAge}</when>" +
            "      </choose>" +
            "  </if>" +
            "  <if test='query.gridId != null'>" +
            "    AND u.gridId = #{query.gridId}" +
            "  </if>" +
            "  <if test='query.begin != null'> AND h.update_time >= #{query.begin} </if>" +
            "  <if test='query.end != null'> AND h.update_time &lt;= DATE_ADD(#{query.end}, INTERVAL 1 DAY) </if>" +
            "  <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "    AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(h.medical_history, ',', numbers.n), ',', -1)) IN " +
            "    <foreach collection='query.diseaseList' item='disease' open='(' separator=',' close=')'>" +
            "      #{disease}" +
            "    </foreach>" +
            "  </if>" +
            "</where>" +
            "GROUP BY diseaseName " +
            "ORDER BY count DESC" +
            "</script>")
    List<DiseaseSummaryDTO.DiseaseStat> selectDiseaseStats(@Param("query") DiseaseStatsQuery query);

    // 查询年龄段统计 - 带条件筛选
    @Select("<script>" +
            "SELECT " +
            "  CASE " +
            "    WHEN age BETWEEN 60 AND 69 THEN '60-69' " +
            "    WHEN age BETWEEN 70 AND 79 THEN '70-79' " +
            "    WHEN age >= 80 THEN '80+' " +
            "  END as ageRange, " +
            "  COUNT(*) as total, " +
            "  COUNT(CASE WHEN has_disease = 1 THEN 1 END) as majorDiseaseCount " +
            "FROM ( " +
            "  SELECT " +
            "    u.id, " +
            "    TIMESTAMPDIFF(YEAR, " +
            "      STR_TO_DATE( " +
            "        CASE " +
            "          WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2)) " +
            "          WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2)) " +
            "        END, '%Y-%m-%d'), " +
            "      CURDATE()) as age, " +
            "    CASE WHEN EXISTS ( " +
            "      SELECT 1 FROM healthinfo h " +
            "      WHERE h.id = u.id AND h.medical_history IS NOT NULL AND h.medical_history != '' " +
            "      <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "        AND (" +
            "        <foreach collection='query.diseaseList' item='disease' separator=' OR '>" +
            "          h.medical_history LIKE CONCAT('%', #{disease}, '%')" +
            "        </foreach>" +
            "        )" +
            "      </if>" +
            "      <if test='query.begin != null'> AND h.update_time >= #{query.begin} </if>" +
            "      <if test='query.end != null'> AND h.update_time &lt;= DATE_ADD(#{query.end}, INTERVAL 1 DAY) </if>" +
            "    ) THEN 1 ELSE 0 END as has_disease " +
            "  FROM userinfo_table u " +
            "  <where>" +
            "    u.identity = 1 " +
            "    AND u.id_card IS NOT NULL AND u.id_card != '' " +
            "    <if test='query.gender != null'> AND u.gender = #{query.gender} </if>" +
            "    <if test='query.gridId != null'>" +
            "      AND u.gridId = #{query.gridId}" +  // 直接使用表中的 gridId 字段
            "    </if>" +
            "  </where>" +
            ") as temp " +
            "WHERE age >= 60 " +
            "  <if test='query.minAge != null'> AND age >= #{query.minAge} </if>" +
            "  <if test='query.maxAge != null'> AND age &lt;= #{query.maxAge} </if>" +
            "GROUP BY ageRange " +
            "ORDER BY ageRange" +
            "</script>")
    List<DiseaseSummaryDTO.AgeStat> selectAgeStats(@Param("query") DiseaseStatsQuery query);

    // 查询性别统计 - 带条件筛选
    @Select("<script>" +
            "SELECT " +
            "  u.gender, " +
            "  CASE u.gender WHEN 1 THEN '男' WHEN 2 THEN '女' END as genderName, " +
            "  COUNT(*) as total, " +
            "  COUNT(CASE WHEN EXISTS ( " +
            "    SELECT 1 FROM healthinfo h " +
            "    WHERE h.id = u.id AND h.medical_history IS NOT NULL AND h.medical_history != '' " +
            "    <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "      AND (" +
            "      <foreach collection='query.diseaseList' item='disease' separator=' OR '>" +
            "        h.medical_history LIKE CONCAT('%', #{disease}, '%')" +
            "      </foreach>" +
            "      )" +
            "    </if>" +
            "    <if test='query.begin != null'> AND h.update_time >= #{query.begin} </if>" +
            "    <if test='query.end != null'> AND h.update_time &lt;= DATE_ADD(#{query.end}, INTERVAL 1 DAY) </if>" +
            "  ) THEN 1 END) as majorDiseaseCount " +
            "FROM userinfo_table u " +
            "<where>" +
            "  u.identity = 1 " +
            "  <if test='query.gender != null'>" +
            "    AND u.gender = #{query.gender}" +
            "  </if>" +
            "  <if test='query.gender == null'>" +
            "    AND u.gender IN (1, 2)" +
            "  </if>" +
            "  <if test='query.minAge != null or query.maxAge != null'>" +
            "    AND (SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE(" +
            "      CASE " +
            "        WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2))" +
            "        WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2))" +
            "      END, '%Y-%m-%d'), CURDATE())) BETWEEN " +
            "      <choose>" +
            "        <when test='query.minAge != null and query.maxAge != null'>#{query.minAge} AND #{query.maxAge}</when>" +
            "        <when test='query.minAge != null'>#{query.minAge} AND 150</when>" +
            "        <when test='query.maxAge != null'>0 AND #{query.maxAge}</when>" +
            "      </choose>" +
            "  </if>" +
            "  <if test='query.gridId != null'>" +
            "    AND u.gridId = #{query.gridId}" +
            "  </if>" +
            "</where>" +
            "GROUP BY u.gender, genderName " +
            "ORDER BY u.gender" +
            "</script>")
    List<DiseaseSummaryDTO.GenderStat> selectGenderStats(@Param("query") DiseaseStatsQuery query);

    // 查询时间范围 - 带条件筛选
    @Select("<script>" +
            "SELECT " +
            "MIN(DATE_FORMAT(h.update_time, '%Y-%m-01')) as start_month, " +
            "MAX(DATE_FORMAT(h.update_time, '%Y-%m-01')) as end_month " +
            "FROM healthinfo h " +
            "INNER JOIN userinfo_table u ON h.id = u.id " +
            "<where>" +
            "  h.medical_history IS NOT NULL " +
            "  AND h.medical_history != '' " +
            "  AND u.identity = 1 " +
            "  <if test='query.gender != null'> AND u.gender = #{query.gender} </if>" +
            "  <if test='query.minAge != null or query.maxAge != null'>" +
            "    AND (SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE(" +
            "      CASE " +
            "        WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2))" +
            "        WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2))" +
            "      END, '%Y-%m-%d'), CURDATE())) BETWEEN " +
            "      <choose>" +
            "        <when test='query.minAge != null and query.maxAge != null'>#{query.minAge} AND #{query.maxAge}</when>" +
            "        <when test='query.minAge != null'>#{query.minAge} AND 150</when>" +
            "        <when test='query.maxAge != null'>0 AND #{query.maxAge}</when>" +
            "      </choose>" +
            "  </if>" +
            "  <if test='query.gridId != null'>" +
            "    AND u.gridId = #{query.gridId}" +
            "  </if>" +
            "  <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "    AND (" +
            "    <foreach collection='query.diseaseList' item='disease' separator=' OR '>" +
            "      h.medical_history LIKE CONCAT('%', #{disease}, '%')" +
            "    </foreach>" +
            "    )" +
            "  </if>" +
            "  <if test='query.begin != null'> AND h.update_time &gt;= #{query.begin} </if>" +
            "  <if test='query.end != null'> AND h.update_time &lt;= DATE_ADD(#{query.end}, INTERVAL 1 DAY) </if>" +
            "</where>" +
            "</script>")
    Map<String, String> selectTimeRange(@Param("query") DiseaseStatsQuery query);

    // 查询每月新增病例 - 带条件筛选
    @Select("<script>" +
            "SELECT COUNT(DISTINCT h.id) as newCases " +
            "FROM healthinfo h " +
            "INNER JOIN userinfo_table u ON h.id = u.id " +
            "<where>" +
            "  h.medical_history IS NOT NULL " +
            "  AND h.medical_history != '' " +
            "  AND u.identity = 1 " +
            "  AND DATE_FORMAT(h.update_time, '%Y-%m-01') = #{monthStart} " +
            "  <if test='query.gender != null'> AND u.gender = #{query.gender} </if>" +
            "  <if test='query.minAge != null or query.maxAge != null'>" +
            "    AND (SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE(" +
            "      CASE " +
            "        WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2))" +
            "        WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2))" +
            "      END, '%Y-%m-%d'), CURDATE())) BETWEEN " +
            "      <choose>" +
            "        <when test='query.minAge != null and query.maxAge != null'>#{query.minAge} AND #{query.maxAge}</when>" +
            "        <when test='query.minAge != null'>#{query.minAge} AND 150</when>" +
            "        <when test='query.maxAge != null'>0 AND #{query.maxAge}</when>" +
            "      </choose>" +
            "  </if>" +
            "  <if test='query.gridId != null'>" +
            "    AND u.gridId = #{query.gridId}" +
            "  </if>" +
            "  <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "    AND (" +
            "    <foreach collection='query.diseaseList' item='disease' separator=' OR '>" +
            "      h.medical_history LIKE CONCAT('%', #{disease}, '%')" +
            "    </foreach>" +
            "    )" +
            "  </if>" +
            "  AND NOT EXISTS ( " +
            "    SELECT 1 FROM healthinfo h2 " +
            "    INNER JOIN userinfo_table u2 ON h2.id = u2.id " +
            "    WHERE h2.id = h.id " +
            "      AND h2.medical_history IS NOT NULL " +
            "      AND h2.medical_history != '' " +
            "      <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "        AND (" +
            "        <foreach collection='query.diseaseList' item='disease' separator=' OR '>" +
            "          h2.medical_history LIKE CONCAT('%', #{disease}, '%')" +
            "        </foreach>" +
            "        )" +
            "      </if>" +
            "      AND h2.update_time &lt; #{monthStart} " +
            "  )" +
            "</where>" +
            "</script>")
    Integer selectNewCasesByMonth(@Param("monthStart") String monthStart, @Param("query") DiseaseStatsQuery query);

    // 查询截止到某月的累计病例 - 带条件筛选
    @Select("<script>" +
            "SELECT COUNT(DISTINCT h.id) as totalWithDisease " +
            "FROM healthinfo h " +
            "INNER JOIN userinfo_table u ON h.id = u.id " +
            "<where>" +
            "  h.medical_history IS NOT NULL " +
            "  AND h.medical_history != '' " +
            "  AND u.identity = 1 " +
            "  AND h.update_time &lt;= LAST_DAY(#{monthStart}) " +
            "  <if test='query.gender != null'> AND u.gender = #{query.gender} </if>" +
            "  <if test='query.minAge != null or query.maxAge != null'>" +
            "    AND (SELECT TIMESTAMPDIFF(YEAR, STR_TO_DATE(" +
            "      CASE " +
            "        WHEN LENGTH(u.id_card) = 18 THEN CONCAT(SUBSTRING(u.id_card, 7, 4), '-', SUBSTRING(u.id_card, 11, 2), '-', SUBSTRING(u.id_card, 13, 2))" +
            "        WHEN LENGTH(u.id_card) = 15 THEN CONCAT('19', SUBSTRING(u.id_card, 7, 2), '-', SUBSTRING(u.id_card, 9, 2), '-', SUBSTRING(u.id_card, 11, 2))" +
            "      END, '%Y-%m-%d'), CURDATE())) BETWEEN " +
            "      <choose>" +
            "        <when test='query.minAge != null and query.maxAge != null'>#{query.minAge} AND #{query.maxAge}</when>" +
            "        <when test='query.minAge != null'>#{query.minAge} AND 150</when>" +
            "        <when test='query.maxAge != null'>0 AND #{query.maxAge}</when>" +
            "      </choose>" +
            "  </if>" +
            "  <if test='query.gridId != null'>" +
            "    AND u.gridId = #{query.gridId}" +
            "  </if>" +
            "  <if test='query.diseaseList != null and query.diseaseList.size() > 0'>" +
            "    AND (" +
            "    <foreach collection='query.diseaseList' item='disease' separator=' OR '>" +
            "      h.medical_history LIKE CONCAT('%', #{disease}, '%')" +
            "    </foreach>" +
            "    )" +
            "  </if>" +
            "  <if test='query.begin != null'> AND h.update_time &gt;= #{query.begin} </if>" +
            "</where>" +
            "</script>")
    Integer selectTotalWithDiseaseByMonth(@Param("monthStart") String monthStart, @Param("query") DiseaseStatsQuery query);

    // 热力图区域统计
    @Select("<script>" +
            "SELECT " +
            "  <choose>" +
            "    <when test='query.level == \"district\"'>" +
            "      CASE " +
            "        WHEN u.address LIKE '%区%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '区', 1), ' ', -1), " +
            "            '区'" +
            "          )" +
            "        WHEN u.address LIKE '%县%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '县', 1), ' ', -1), " +
            "            '县'" +
            "          )" +
            "        WHEN u.address LIKE '%市%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '市', 1), ' ', -1), " +
            "            '市'" +
            "          )" +
            "        ELSE " +
            "          SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, ' ', 2), ' ', -1)" +
            "      END as regionName" +
            "    </when>" +
            "    <when test='query.level == \"street\"'>" +
            "      CASE " +
            "        WHEN u.address LIKE '%街道%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '街道', 1), ' ', -1), " +
            "            '街道'" +
            "          )" +
            "        WHEN u.address LIKE '%镇%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '镇', 1), ' ', -1), " +
            "            '镇'" +
            "          )" +
            "        WHEN u.address LIKE '%乡%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '乡', 1), ' ', -1), " +
            "            '乡'" +
            "          )" +
            "        ELSE " +
            "          SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, ' ', 3), ' ', -1)" +
            "      END as regionName" +
            "    </when>" +
            "    <otherwise>" +
            "      CASE " +
            "        WHEN u.address LIKE '%社区%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '社区', 1), ' ', -1), " +
            "            '社区'" +
            "          )" +
            "        WHEN u.address LIKE '%村%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '村', 1), ' ', -1), " +
            "            '村'" +
            "          )" +
            "        WHEN u.address LIKE '%小区%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '小区', 1), ' ', -1), " +
            "            '小区'" +
            "          )" +
            "        WHEN u.address LIKE '%大街%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '大街', 1), ' ', -1), " +
            "            '大街'" +
            "          )" +
            "        WHEN u.address LIKE '%路%' THEN " +
            "          CONCAT(" +
            "            SUBSTRING_INDEX(SUBSTRING_INDEX(u.address, '路', 1), ' ', -1), " +
            "            '路'" +
            "          )" +
            "        ELSE " +
            "          SUBSTRING_INDEX(u.address, ' ', -1)" +
            "      END as regionName" +
            "    </otherwise>" +
            "  </choose>," +
            "  #{query.level} as level, " +
            "  COUNT(DISTINCT u.id) as elderlyCount, " +
            "  COUNT(DISTINCT CASE WHEN h.medical_history IS NOT NULL AND h.medical_history != '' " +
            "    <if test='query.begin != null'> AND h.update_time &gt;= #{query.begin} </if>" +
            "    <if test='query.end != null'> AND h.update_time &lt;= DATE_ADD(#{query.end}, INTERVAL 1 DAY) </if>" +
            "    THEN h.id END) as majorDiseaseCount " +
            "FROM userinfo_table u " +
            "LEFT JOIN healthinfo h ON u.id = h.id " +
            "WHERE u.identity = 1 " +
            "  AND u.address IS NOT NULL " +
            "  AND u.address != '' " +
            "  <if test='query.gridId != null'> AND u.gridId = #{query.gridId} </if>" +
            "GROUP BY regionName " +
            "HAVING elderlyCount > 0 " +
            "ORDER BY elderlyCount DESC" +
            "</script>")
    List<DiseaseHeatmapDTO.RegionStat> selectRegionStats(@Param("query") DiseaseHeatmapQuery query);



    // 趋势统计 - 返回固定数据
    // 趋势统计 - 直接返回null，在Service中手动创建数据
    @Select("SELECT 1 FROM DUAL")
    DiseaseSummaryDTO.DiseaseTrend selectTrendStats();
}